package studysystem.demo.mapper;

import org.apache.ibatis.annotations.*;
import studysystem.demo.bean.*;

import java.util.List;

@Mapper
public interface LearnerMapper {
    //获取用户角色
    @Select("select role.role from role,learner where phone = #{phone,jdbcType=VARCHAR} and learner.role_id=role.id")
    Role findRole(String phone);
    //获取用户权限---UNDO
    @Select("select * from permission where role = #{role,jdbcType=VARCHAR}")
    List<Permission> findPermission(String role);

    /**新增用户角色
    @Insert("insert into role(username, role) values (#{username,jdbcType=VARCHAR}, #{role,jdbcType=VARCHAR})")
    void saveRole(Role roleEntity);

    //删除用户角色
    @Delete("delete from role where username = #{username,jdbcType=VARCHAR}")
    void deleteRole(String username);
     **/
    //修改学习者密码
    @Update("update learner set password=#{password,jdbcType=VARCHAR} where phone=#{phone,jdbcType=VARCHAR}")
    void updatePsw(Learner learnerEntity);

    //新建学习者
    @Insert("insert into learner (userid, phone, password, name, sex, role_id) values (#{userid,jdbcType=VARCHAR}," +
            " #{phone,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR},1001)")
    void saveLearner(Learner learnerEntity);

    //更新学习者
    @Update("update learner set name = #{name,jdbcType=VARCHAR}, sex = #{sex,jdbcType=VARCHAR}, userid = #{userid,jdbcType=VARCHAR} where phone = #{phone,jdbcType=VARCHAR}")
    void updateLearner(Learner learnerEntity);

    //删除学习者
    @Delete("delete from learner where phone = #{phone,jdbcType=VARCHAR}")
    void deleteLearner(String phone);

    //查询学习者信息
    @Select("select phone, password, name, sex, userid, role.role from role,learner where phone = #{phone,jdbcType=VARCHAR} and role.id=1001")
    Learner findLearnerInfo(String phone);

    //查询学习者密码
    @Select("select password from learner where phone = #{phone,jdbcType=VARCHAR}")
    Learner findLearnerPsw(String phone);

    //查询是否存在该学习方向
    @Select("select coursedir from course_class where coursedir = #{class_dir,jdbcType=VARCHAR}")
    String checkCoursedir(String class_dir);

    //查询课程所属方向id
    @Select("select class_id from course where id=#{id,jdbcType=INTEGER}")
    int findCoursedirid(int id);
    //查询大类id所属方向
    @Select("select coursedir from course_class where id = #{id,jdbcType=INTEGER}")
    String findCoursedir(int id);
    //查询学习者课程id
    @Select("select course_id from studying where learner_phone=#{phone,jdbcType=VARCHAR}")
    int findCourseid(String phone);
    //选择、修改学习方向
    @Update("update studying set course_dir = #{course_dir,jdbcType=VARCHAR} where phone = #{phone,jdbcType=VARCHAR}")
    void updateCoursedir(@Param("phone")String phone, @Param("course_dir")String course_dir);

    //查询所有学习者条数
    @Select("select count(*) from learner")
    int findLearnerCountAll();

    //查询所有学习者
    @Select("select phone, name, sex, userid, role.role from role,learner where role.id=1001 order by name desc")
    List<Learner> findLearnerAll();

    //查询学习者条数 (用户名  模糊查询)
    @Select("select count(*) from learner where phone like CONCAT(CONCAT('%', #{phone}), '%')")
    int findLearnerCountByPhone(String phone);

    //查询学习者(用户名  模糊查询)
    @Select("select phone, name, sex, userid, role.role from role,learner where phone like CONCAT(CONCAT('%', #{phone}), '%') and role.id = 1001 order by name desc")
    List<Learner> findLearnerByPhone(String phone);

    //查询学习者条数 (姓名  模糊查询)
    @Select("select count(*) from learner where name like CONCAT(CONCAT('%', #{name}), '%') and role_id = 1001")
    int findLearnerCountByName(String name);

    //查询学习者用户(姓名  模糊查询)
    @Select("select phone, name, sex, userid, role.role  from role,learner where name like CONCAT(CONCAT('%', #{name}), '%') and role_id = 1001 order by name desc")
    List<Learner> findLearnerByName(String name);
    @Select("select phone   from learner where name = #{name,jdbcType=VARCHAR}")
    String findLearnerPhoneByName(String name);
    //检验是否存在学习者
    @Select("select name from learner where name like CONCAT(CONCAT('%', #{name}), '%') ")
    String findLearnerByName2(String name);


}
